﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Reflection;
using System.Text;

namespace Utility
{
    public class DBSqlHelper : IDisposable
    {
        private DbHelper dbhelper = null;

        public DBSqlHelper(DbHelper dbhelper)
        {
            this.dbhelper = dbhelper;
        }

        public void Insert(string TableName, object obj, out string SqlStr, out List<DbParameter> Param)
        {
            Type BaseType = obj.GetType();
            bool IsfristField = true;//是否第一字段
            StringBuilder StrB = new StringBuilder("INSERT INTO " + TableName + "(");
            StringBuilder StrV = new StringBuilder("VALUES(");

            List<DbParameter> ListParam = new List<DbParameter>();
            PropertyInfo[] PropertyInfos = BaseType.GetProperties();
            foreach (PropertyInfo pi in PropertyInfos)
            {
                Type pitype = pi.PropertyType;
                object pivalue = pi.GetValue(obj, null);
                if (pivalue != null)
                {
                    if ((pitype.Equals(typeof(double)) || pitype.Equals(typeof(long)) || pitype.Equals(typeof(int)) || pitype.Equals(typeof(Int32)) || pitype.Equals(typeof(Int64)) || pitype.Equals(typeof(Int16)) || pitype.Equals(typeof(float)) || pitype.Equals(typeof(decimal))) && double.Parse(pivalue.ToString()) == 0)
                    {
                        continue;
                    }

                    if (IsfristField)
                    {
                        StrB.Append(pi.Name);
                        StrV.Append("@" + pi.Name);
                        IsfristField = false;
                    }
                    else
                    {
                        StrB.Append("," + pi.Name);
                        StrV.Append(",@" + pi.Name);
                    }

                    ListParam.Add(dbhelper.CreateParameter("@" + pi.Name, pi.GetValue(obj, null)));
                }
            }
            StrB.Append(")");
            StrV.Append(")");
            SqlStr = StrB.ToString() + StrV.ToString();
            Param = ListParam;
        }

        public void UPDATE(string TableName, object obj, string PrimaryKeyField, out string SqlStr, out List<DbParameter> Param)
        {
            Type BaseType = obj.GetType();
            bool IsfristField = true;//是否第一字段
            StringBuilder StrB = new StringBuilder("UPDATE " + TableName + " SET ");
            StringBuilder StrW = new StringBuilder(" WHERE ");

            List<DbParameter> ListParam = new List<DbParameter>();
            PropertyInfo[] PropertyInfos = BaseType.GetProperties();
            foreach (PropertyInfo pi in PropertyInfos)
            {
                Type pitype = pi.PropertyType;
                object pivalue = pi.GetValue(obj, null);
                if (pivalue != null)
                {
                    if ((pitype.Equals(typeof(double)) || pitype.Equals(typeof(int)) || pitype.Equals(typeof(float)) || pitype.Equals(typeof(decimal))) && double.Parse(pivalue.ToString()) == 0)
                    {
                        continue;
                    }
                    if (pi.Name.ToLower().Equals(PrimaryKeyField.ToLower()))
                    {
                        StrW.Append(" " + pi.Name + "=@" + pi.Name);
                    }
                    else
                    {
                        if (IsfristField)
                        {
                            StrB.Append(pi.Name + "=@" + pi.Name);

                            IsfristField = false;
                        }
                        else
                        {
                            StrB.Append("," + pi.Name + "=@" + pi.Name);
                        }
                    }

                    ListParam.Add(dbhelper.CreateParameter("@" + pi.Name, pi.GetValue(obj, null)));
                }
            }

            SqlStr = StrB.ToString() + StrW.ToString();
            Param = ListParam;
        }

        #region Dispose

        public void Dispose()
        {
            dbhelper.Dispose();
            dbhelper = null;
            GC.Collect();
        }

        ~DBSqlHelper()
        {
            Dispose();
        }

        #endregion Dispose
    }
}